package org.compiere.apps.search;

import java.awt.Frame;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;

import org.adempiere.plaf.AdempierePLAF;
import org.compiere.apps.ALayout;
import org.compiere.minigrid.IDColumn;
import org.compiere.model.MTable;
import org.compiere.model.X_C_ValidCombination;
import org.compiere.swing.CLabel;
import org.compiere.swing.CTextField;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

public class InfoContractCMS extends Info 
{	
	private static final long serialVersionUID = -2866234663806367633L;

	protected InfoContractCMS(Frame frame, boolean modal, int WindowNo, String value,boolean multiSelection, String whereClause) 
	{
		super (frame, modal, WindowNo, "c", "CMS_Contract_ID", multiSelection, whereClause);
		
		log.info("InfoContractCMS");
		setTitle(Msg.getMsg(Env.getCtx(), "InfoContractCMS"));
		
		try
		{
			statInit();
			p_loadedOK = initInfo ();
		}
		catch (Exception e)
		{
			return;
		}
		
		int no = p_table.getRowCount();
		setStatusLine(Integer.toString(no) + " " + Msg.getMsg(Env.getCtx(), "SearchRows_EnterQuery"), false);
		setStatusDB(Integer.toString(no));
		if (value != null && value.length() > 0)
		{
			fcontractNumber.setValue(value);
			executeQuery();
		}
		//
		pack();
		//	Focus
		fcontractNumber.requestFocus();	
	}
	
	private CLabel ldescription = new CLabel(Msg.translate(Env.getCtx(), "Description"));
	private CTextField fdescription =new CTextField(20);	
	//  Static Info
	private CLabel lcontractNumber = new CLabel(Msg.translate(Env.getCtx(), "CMS_Contract_ContractNum"));
	private CTextField fcontractNumber =new CTextField(5);
	
	private CLabel lContragent = new CLabel(Msg.translate(Env.getCtx(), "C_BPartner_ID"));
	private CTextField fContragent = new CTextField(10);
	
	private CLabel ldepartmentName = new CLabel(Msg.translate(Env.getCtx(), "HR_Department_ID"));
	private CTextField fdepartmentName =new CTextField(10);	
	
	private CLabel ltypeName = new CLabel(Msg.translate(Env.getCtx(), "cms_contractstype_ID"));
	private CTextField ftypeName =new CTextField(10);	
	
	private void statInit() throws Exception
	{		
		ldescription.setLabelFor(fdescription);
		fdescription.setBackground(AdempierePLAF.getInfoBackground());
		fdescription.addActionListener(this);
		
		lcontractNumber.setLabelFor(fcontractNumber);
		fcontractNumber.setBackground(AdempierePLAF.getInfoBackground());
		fcontractNumber.addActionListener(this);
		
		lContragent.setLabelFor(fContragent);
		fContragent.setBackground(AdempierePLAF.getInfoBackground());
		fContragent.addActionListener(this);
		
		ldepartmentName.setLabelFor(fdepartmentName);
		fdepartmentName.setBackground(AdempierePLAF.getInfoBackground());
		fdepartmentName.addActionListener(this);
				
		ltypeName.setLabelFor(ftypeName);
		ftypeName.setBackground(AdempierePLAF.getInfoBackground());
		ftypeName.addActionListener(this);
		//
		parameterPanel.setLayout(new ALayout(2,2,true));
		
		parameterPanel.add(ldescription, null);
		parameterPanel.add(fdescription, null);
		
		parameterPanel.add(lcontractNumber,null);
		parameterPanel.add(fcontractNumber,null);
		
		parameterPanel.add(lContragent, null);
		parameterPanel.add(fContragent, null);
		
		parameterPanel.add(ldepartmentName,null);
		parameterPanel.add(fdepartmentName,null);
		
		parameterPanel.add(ltypeName,null);
		parameterPanel.add(ftypeName,null);	
	}

	@SuppressWarnings("deprecation")
	private String getPayableContracts(Timestamp currentDate)
	{
		StringBuilder builder = new StringBuilder();
		builder.append("(SELECT c.CMS_Contract_ID");
		builder.append(" \nFROM CMS_Contract c");
//		builder.append(" \nINNER JOIN CMS_Payment p ON (c.CMS_Contract_ID = p.CMS_Contract_ID)");
//		builder.append(" \nWHERE EXTRACT(Month FROM p.DateAcct) = ");
//		builder.append(currentDate.getMonth() + 1);
//		builder.append(" \nAND EXTRACT(Year FROM  p.DateAcct) = ");
//		builder.append(currentDate.getYear() + 1900);
		builder.append(")");
		
		return builder.toString();
	}
	
	/**
	 *	General Init
	 *	@return true, if success
	 * @throws SQLException 
	 */
	private boolean initInfo () throws SQLException
	{
	//  Set Defaults (V.Sokolov)  Asset...
		StringBuffer where = new StringBuffer("c.IsActive='Y'");
		
		int C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_BPartner_ID");
		
		if(C_BPartner_ID == 0){
			C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "Initiator_ID");
			if(C_BPartner_ID > 0)
				C_BPartner_ID = DB.getSQLValue(null, "select c_bpartner_id from adempiere.ad_user where ad_user_id = " + C_BPartner_ID);
		}
		
//		int depID = DB.getSQLValue(null, "select c.hr_department_id		\n" + 
//				"from adempiere.hr_contract c		\n" +
//				"where c.c_bpartner_id = "+C_BPartner_ID
//				);
		
		int depID = DB.getSQLValue(null, "select hr_department_id		\n" + 
				"from adempiere.hr_employee 		\n" +
				"where c_bpartner_id = "+ C_BPartner_ID +"		\n" +
				"group by 1 " );
		
		int AD_Table_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "AD_Table_ID");
		
		MTable table = new MTable(Env.getCtx(), AD_Table_ID, null);
		
		if(AD_Table_ID > 0 && table.getName().equals("Finance Application"))
		{
			
			Timestamp currentTime = new Timestamp(Calendar.getInstance().getTime().getTime());
			where.append(" AND c.CMS_Contract_ID IN " + getPayableContracts(currentTime));
			if(depID > 0)
			where.append(" and d.HR_Department_ID = " + depID);
		}
		else if(AD_Table_ID > 0 && table.getName().equals("TRM_Deposit"))
		{
			where.append(" AND c.Deposits = 'Y'");
		}
		else
		{			
			if(C_BPartner_ID <= 0)
			{
				int C_ValidCombination_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_ValidCombination_ID");
				if(C_ValidCombination_ID > 0)
				{
					X_C_ValidCombination combination = new X_C_ValidCombination(Env.getCtx(), C_ValidCombination_ID, null);
					C_BPartner_ID = combination.getC_BPartner_ID();
				}
			}
			
			if(C_BPartner_ID > 0)
					where.append(" and c.c_bpartner_id = " + String.valueOf(C_BPartner_ID) + " OR LOWER(c.cms_contract_contractnum) LIKE 'без договора'");
		}
			
			
		prepareTable (c_chargeLayout,
				"\n CMS_Contract c " +
				"\n INNER JOIN C_BPartner b ON (c.C_BPartner_ID = b.C_BPartner_ID) " +
				"\n LEFT JOIN HR_Department d ON (c.HR_Department_ID = d.HR_Department_ID) " +
				"\n LEFT JOIN CMS_ContractsType t ON (c.CMS_ContractsType_ID = t.CMS_ContractsType_ID) ",
				where.toString(),
				"c.CMS_Contract_ContractNum");
		return true;
	}
	
	@Override
	protected String getSQLWhere() {
		
		StringBuffer sql = new StringBuffer();
		if(fdescription.getText().length() > 0)
			sql.append(" AND c.Description LIKE ?");		
		if (fcontractNumber.getText().length() > 0)
			sql.append(" AND c.CMS_Contract_ContractNum LIKE ?");
		if (fContragent.getText().length() > 0)
			sql.append(" AND UPPER(b.name) LIKE ?");
		if (fdepartmentName.getText().length() > 0)
			sql.append(" AND UPPER(d.name) LIKE ?");
		if (ftypeName.getText().length() > 0)
			sql.append(" AND UPPER(t.name) LIKE ?");

		return sql.toString();
	}
	
	private static final Info_Column[] c_chargeLayout = {
		
		//C_Charge_ID field
		new Info_Column(Msg.translate(Env.getCtx(), "CMS_Contract_ID"),
				"c.CMS_Contract_ID", IDColumn.class),
		new Info_Column(Msg.translate(Env.getCtx(), "Description"), "c.Description", String.class),
		//1-field for charge code		
		new Info_Column(Msg.translate(Env.getCtx(),"CMS_Contract_ContractNum"),
						"c.CMS_Contract_ContractNum",String.class),
		//2-field for charge name
		new Info_Column(Msg.translate(Env.getCtx(), "C_BPartner_ID"),
								"b.name as bpName", String.class),
		//3-field for charge type name
		new Info_Column(Msg.translate(Env.getCtx(), "HR_Department_ID"),
						"d.name as departmentName", String.class),
		new Info_Column(Msg.translate(Env.getCtx(), "cms_contractstype_ID"),
								"t.name as contractName", String.class),
		new Info_Column(Msg.translate(Env.getCtx(), "CMS_Contract_CreatDate"),
								"c.CMS_Contract_CreatDate", String.class),
		new Info_Column(Msg.translate(Env.getCtx(), "IsActive"),
					"c.IsActive", Boolean.class)
	};
	
	@Override
	protected void setParameters(PreparedStatement pstmt, boolean forCount)
			throws SQLException {
		
		int index = 1;
		if(fdescription.getText().length() > 0)
			pstmt.setString(index++, getSQLText(fdescription));
		if (fcontractNumber.getText().length() > 0)
			pstmt.setString(index++, getSQLText(fcontractNumber));
		if (fContragent.getText().length() > 0)
			pstmt.setString(index++, getSQLText(fContragent));
		if (fdepartmentName.getText().length() > 0)
			pstmt.setString(index++, getSQLText(fdepartmentName));
		if (ftypeName.getText().length() > 0)
			pstmt.setString(index++, getSQLText(ftypeName));
	}
	
	/**
	 *  Get SQL WHERE parameter
	 *  @param f field
	 *  @return Upper case text with % at the end
	 */
	private String getSQLText (CTextField f)
	{
		String s = f.getText().toUpperCase();
		if (!s.endsWith("%"))
			s = "%"+s+"%";
		log.fine( "String=" + s);
		return s;
	} 
}
